多資料表查詢,可將關聯表正規劃分割的資料表,還原成使用者所需要的資訊。
合併查詢就是將儲存在不同資料表的欄位資料,利用資料表之間欄位的關連性來結合多資料表之所需要的資訊。常用的合併查詢如下:
1.inner Join 內部合併查詢
2.left (outer) Join 左外部合併查詢
3.right (outer) Join 右外部合併查詢
4.full (outer) Join 完全外部合併查詢
5.cross Join 交叉合併查詢
(自繪圖上色不是很好請見諒><)
INNER JOIN (內部連接) 為內部合併查詢指令,可以取回2個資料表都共同存在合併欄位的記錄資料。也就是在「兩個資料表都有在 on 關鍵字所指定的欄位的值」這個條件下才取出想取的欄位。
select 想收尋的欄位(資料表A & B都可) from [資料表A] inner Join [資料表B] on [資料表A].[關聯鍵] =[資料表B].[關聯鍵]
以這個網站為範例。
Cumstomers資料表取出CustomerID與CustomerName欄位,和從Orders資料表取出OrderID欄位,合併的欄位是CustomerID,如下程式碼:
SELECT Customers.CustomerID,Customers.CustomerName ,Orders.OrderID FROM Customers inner join Orders on Customers.CustomerID = Orders.CustomerID;
Cumstomers資料表使用inner Join合併Orders資料表的欄位資料,合併欄位是on後的CustomerID欄位。
顯示結果CumstomerID沒有1是因為Orders資料表沒有CumstomerID 1 的資料,因為inner Join會顯示兩個資料表所重複的資料。
left Join (左外部合併查訊)是在合併兩個資料表中,取回左邊資料表的所有紀錄,就算在右邊資料表沒有存在合併欄位的值,顯示結果會以左邊資料表為主。
select 想收尋的欄位(資料表A & B都可) from [資料表A] left Join [資料表B] on [資料表A].[關聯鍵] =[資料表B].[關聯鍵]
左邊是指在left join 左邊的資料表名稱也就是上程式的[資料表A]。
SELECT Customers.CustomerID,Customers.CustomerName ,Orders.OrderID FROM Customers left join Orders on Customers.CustomerID = Orders.CustomerID;
使用left Join後會發現,CustomerID 1 會顯示出來,因為在Customers資料表中有這筆資料,才不管右邊的Orders資料表有沒有這筆資料呢哼!不過CustomerID 1的OrderID那欄資料就會是Null。
就跟left相反XD
right Join (右外部合併查訊)是在合併兩個資料表中,取回右邊資料表的所有紀錄,就算在左邊資料表沒有存在合併欄位的值,顯示結果會以右邊資料表為主。
select 想收尋的欄位(資料表A & B都可) from [資料表A] right Join [資料表B] on [資料表A].[關聯鍵] =[資料表B].[關聯鍵]
右邊是指在right join 右邊的資料表名稱也就是上程式的[資料表B]。
SELECT Customers.CustomerID,Customers.CustomerName ,Orders.OrderID FROM Customers right join Orders on Customers.CustomerID = Orders.CustomerID;
使用right Join顯示結果會以Orders資料表為主,會顯示Orders資料表的全部資料,就算Customers資料表沒有(就會用Null呈現)。不過這範例Orders資料表的資料好像在Customers都有,所以顯示的結果跟inner Join的結果一樣只有排序不一樣而已。
不同於left Join & right Join , full Join(完全外部合併)可以取回左右兩邊資料表的所有紀錄。
select 想收尋的欄位(資料表A & B都可) from [資料表A] full Join [資料表B] on [資料表A].[關聯鍵] =[資料表B].[關聯鍵]
用網站的資料庫範例使用full Join 好像會出錯XD
SELECT * FROM Products full join OrderDetails on Products.ProductID= OrderDetails.ProductID;
結果:
「An unspecified error occurred.」
理論上以該是可以成功的==畢竟left join 跟 right join 都可成功XD。
不過可以看 w3c 介紹full Join的網站裡的範例來參考,但若是複製裡面的程式碼到網站一樣不能執行QQ
不過最重要的事,知道full Join就是將Customers和Orders的資料左右全部合併,不管有沒有空的資料。
交叉合併查詢(Cross Join )是關聯式代數的卡迪生乘績運算,查詢結果的紀錄是2個資料表紀錄的乘積。
將兩個資料表所有想取出欄位的資料的全部結果都排列組合出來,不需要加 on 關鍵字。
Select * From 表A Cross Join 表B;
也就是說表A有5筆紀錄,表B有8筆紀錄,交查合併後會得到5 * 8=40筆的紀錄,如果沒有指名欄位,欄位數就是2個資料表的欄位總數。
範例可參考這裡
在SQL Server 可以使用工具精靈直接顯示出join的SQL語法,就不用手打了,而且當資料表不止兩個時會打到瘋掉吧XD,所以能拉工具就拉工具吧哈哈
首先在新增查詢右鍵在編輯中設計查詢
選擇要的資料表後,如果沒拉過線(主索引鍵 & 外部索引鍵)會預設相同名稱的欄位幫忙拉線,可以對著線按右鍵移除就好了。
然後再將自己的主索引及外部拉好,自己拉好線後,在下方就會有inner Join的SQL語法了XD,按確定後再執行就可以看到結果了。
如果要left Join 可以對線右鍵將選擇Orders資料表打勾勾,下方就會有left Join的SQL語法出現了。
阿假如要right Join 就跟上面一樣對線右鍵,不過是選Customers資料表打勾勾,下方就會有right Join的SQL語法出現了XD
full Join就兩個資料表都打勾勾就會有full Join的SQL了。
而兩個資料表以上也是可以很方邊的產生join SQL語法><
總之善用精靈工具可以節省不少時間XD